// Copyright 2022 a.o.smith ALL Rights Reserved
package com.liuxinlong.common;

import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.stream.Collectors;

import com.liuxinlong.utils.ObjectUtils;
import org.apache.commons.collections4.map.HashedMap;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.stereotype.Service;

import com.liuxinlong.enums.ReturnCodes;
import com.liuxinlong.utils.ExcelUtils;

import lombok.extern.slf4j.Slf4j;

/**
 * 类描述
 * <p>
 * 此类关联设计的功能
 *
 * @author liuxinlong@hotwater.com.cn
 * @version 1.0.0
 * @since 2022年1月22日
 */
@Slf4j
@Service
public class ExcelImportService {

	public ImportResp excelFile(String filePath, int startRow, ExcelImportCallBack callBack) {
		startRow = startRow <= 1 ? 0 : (startRow - 1);
		List<String[]> fileContent;
		try {
			fileContent = ExcelUtils.importExcel(filePath, startRow);
		} catch (Exception e) {
			return new ImportResp(ReturnCodes.HTTP_IMPORT_FAIL_READ_FILE.getRetCode(), "读取文件失败，文件路径=" + filePath);
		}

		if (fileContent.isEmpty()) {
			return new ImportResp(ReturnCodes.HTTP_IMPORT_FAIL_NO_DATA.getRetCode(), "导入失败，文件没有数据");
		}
		Map<Integer, String[]> errorRowMap = new HashedMap<>();
		int length = fileContent.get(0).length;
		for (int i = 0; i < fileContent.size(); i++) {
			log.info("第{}行数据解析中", i);
			String[] record = fileContent.get(i);
//			System.out.println(Arrays.toString(record));
//			System.out.println(record[1]);
			System.out.println(record.length);
//			String[] errorRecord = new String[record.length + 1];
			String[] errorRecord = new String[length + 1];
			System.arraycopy(record, 0, errorRecord, 0, length);
			Collection<RowError> errors = new HashSet<>();
			try {
				errors = callBack.checkRow(record);
			} catch (Exception e) {
				errorRecord[length] = e.getMessage();
				errorRowMap.put(i, errorRecord);
				continue;
			}
			if (errors.size() > 0) {
				Collection<String> errorStrs = errors.stream().map(RowError::getErrorMessage)
						.collect(Collectors.toList());
				errorRecord[length] = StringUtils.join(errorStrs, ";");
				errorRowMap.put(i, errorRecord);
				continue;
			}
			try {
				boolean persistResult = callBack.persistRowToDb(record);
				if (!persistResult) {
//					errorRecord[length] = "持久化数据失败";
					errorRowMap.put(i, errorRecord);
					continue;
				}
			} catch (Exception e) {
				errorRecord[length] = e.getMessage();
				errorRowMap.put(i, errorRecord);
				continue;
			}
		}

		if (errorRowMap.size() > 0) {
			int total = fileContent.size();
			int failedTotal = errorRowMap.size();
			int successTotal = total - failedTotal;
			ImportResp resp = new ImportResp();
			resp.setRetCode(ReturnCodes.HTTP_IMPORT_PART_FAIL.getRetCode());
			resp.setTotal(total);
			resp.setSuccessTotal(successTotal);
			resp.setFailedTotal(failedTotal);
			List<String[]> errorList = new ArrayList<>();
			TreeMap<Integer, String[]> treeMap = new TreeMap<>(errorRowMap);
			treeMap.forEach((k, v) -> errorList.add(v));
			List<Map> faiList = callBack.convertFailList(errorList);
			resp.setFailedList(faiList);
			return callBack.partFailure(resp);
		}

		ImportResp resp = new ImportResp();
		resp.setTotal(fileContent.size());
		resp.setSuccessTotal(resp.getTotal());
		return callBack.allSuccess(resp);
	}

	/**
	 * 解析多sheet页文件
	 *
	 * @param filePath    文件路径
	 * @param startRowMap 数据开始行数map
	 * @return 多sheet页数据map
	 */
	public static Map<String, List<String[]>> importExcelMultipleSheet(String filePath, Map<String, Integer> startRowMap) {
		log.info("导入解析开始，filepath：{}", filePath);
		Map<String, List<String[]>> dataMap = new HashMap<>();
		try {
			InputStream inputStream = new FileInputStream(filePath);
			Workbook workbook = WorkbookFactory.create(inputStream);
			List<? extends Name> sheetNameList = workbook.getAllNames();
			List<? extends Name> sheetAvalibleList = sheetNameList.stream().filter(item->item.getSheetIndex() > 0).collect(Collectors.toList());
			for (Name item: sheetAvalibleList) {
				System.out.println(" sheet name: " + item.getSheetName());
				System.out.println(" sheet index: " + item.getSheetIndex());
				System.out.println(" sheet namename: " + item.getNameName());
				System.out.println(" sheet comment: " + item.getComment());
			}
			for (String key : startRowMap.keySet()) {
				int startRow = startRowMap.get(key);
				List<String[]> list = new ArrayList<>();
				Sheet sheet = workbook.getSheet(key);
				if (ObjectUtils.isEmpty(sheet)) {
					log.info("sheet {} 不存在...",key);
					continue;
				}
				System.out.println("解析sheet: " + sheet.getSheetName());
				int rows = sheet.getPhysicalNumberOfRows();
				for (int i = 0; i < rows; i++) {
					if (i >= startRow) {
						Row row = sheet.getRow(i);
						String[] objects = new String[row.getPhysicalNumberOfCells()];
						int index = 0;
						boolean allEmpty = true;
						for (Cell cell : row) {
							objects[index] = getCellValue(cell);
							if (StringUtils.isNotEmpty(objects[index]))
								allEmpty = false;
							index++;
						}
						if (!allEmpty)
							list.add(objects);
						log.info("解析文件第{}行成功！", i);
					}
				}
				System.out.println("sheet: " + sheet.getSheetName() + "解析完成");
				dataMap.put(key, list);
			}

			log.info("导入文件解析成功！");
			return dataMap;
		} catch (Exception e) {
			log.info("导入文件解析失败！");
			e.printStackTrace();
			throw new AosException(ReturnCodes.HTTP_IMPORT_FAIL_READ_FILE, "文件解析失败！");
		}
	}

	public static String getCellValue(Cell cell) {
		String cellValue = "";
		if (cell == null)
			return cellValue;
		switch (cell.getCellType().ordinal()) {
			case 1:
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					SimpleDateFormat sdf = null;
					if (cell.getCellStyle().getDataFormat() == 14) {
						sdf = new SimpleDateFormat("yyyy/MM/dd");
					} else if (cell.getCellStyle().getDataFormat() == 21) {
						sdf = new SimpleDateFormat("HH:mm:ss");
					} else if (cell.getCellStyle().getDataFormat() == 22) {
						sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
					} else {
						throw new RuntimeException("日期格式错误!!");
					}
					Date date = cell.getDateCellValue();
					cellValue = sdf.format(date);
				} else if (cell.getCellStyle().getDataFormat() == 0) {
					cell.setCellType(CellType.STRING);
					cellValue = String.valueOf(cell.getRichStringCellValue().getString());
				}
				return cellValue;
			case 2:
				cellValue = String.valueOf(cell.getStringCellValue());
				return cellValue;
			case 3:
				cellValue = String.valueOf(cell.getBooleanCellValue());
				return cellValue;
			case 4:
				cell.setCellType(CellType.STRING);
				cellValue = String.valueOf(cell.getRichStringCellValue().getString());
//                cellValue = String.valueOf(cell.getCellFormula());
				return cellValue;
			case 5:
				cellValue = null;
				return cellValue;
			case 6:
				cellValue = "非法字符";
				return cellValue;
		}
		return cellValue;
	}
}
